****prepare amenity data that goes into model for robustness checks


//crime
import excel using "$data/Amenities/crime_2000.xlsx", clear firstrow
drop if State == "" | State == "State"
ren *, lower
keep state violent property
destring violent property, replace
replace violent = violent*10
egen avg = rowmean(violent property)
replace avg = avg/1000
su avg
local mincrime = `r(min)'
replace avg = avg-`mincrime'
ren avg crime_2000
keep state crime_2000
ren state state_full
merge 1:1 state_full using "$data/Crosswalks/state_fips_crosswalk", keep(match) nogen
ren statefips stfips
keep stfips crime
save "$temp/state_crime_2000", replace

import excel using "$data/Amenities/crime_2010.xlsx", clear firstrow
drop if State == "" | State == "State"
ren *, lower
keep state violent property
destring violent property, replace
replace violent = violent*10
egen avg = rowmean(violent property)
replace avg = avg/1000
replace avg = avg-`mincrime'
ren avg crime_2010
keep state crime_2010
ren state state_full
merge 1:1 state_full using "$data/Crosswalks/state_fips_crosswalk", keep(match) nogen
ren statefips stfips
keep stfips crime
save "$temp/state_crime_2010", replace

//establishments per capita
import delimited "$data/Amenities/est_2000.csv", clear
drop if state == 0
keep if naicsdscr == "Total"
keep if entrsizedscr == "Total"
drop if state == 11
ren state stfips
keep stfips estb
ren estb est_2000
merge 1:1 stfips using "$temp/state_populations_dynamic", keep(match) nogen
drop pop_2010
replace pop_2000 = pop_2000 * 1000000
replace est = est/pop_2000 //per capita!
replace est = est*100
su est
local minest = `r(min)'
replace est = est-`minest'
keep stfips est_2000
save "$temp/state_est_2000", replace


import delimited "$data/Amenities/est_2010.csv", clear
drop if state == 0
keep if naicsdscr == "Total"
keep if entrsizedscr == "Total"
drop if state == 11
ren state stfips
keep stfips estb
ren estb est_2010
merge 1:1 stfips using "$temp/state_populations_dynamic", keep(match) nogen
drop pop_2000
replace pop_2010 = pop_2010 * 1000000
replace est = est/pop_2010 //per capita!
replace est = est*100
replace est = est-`minest'
keep stfips est_2010
save "$temp/state_est_2010", replace


//cooling
import delimited "$data/Amenities/cooling.csv", clear
keep v11 v9
ren v9 cooling 
ren v11 state_full
replace state_full = subinstr(state_full, "_", " ", .)
replace state_full = strproper(state_full)
merge 1:1 state_full using "$data/Crosswalks/state_fips_crosswalk", keep(match) nogen
replace cooling = cooling/1000
keep cooling statefips
ren statefips stfips
su cooling
replace cooling = cooling - `r(min)'
save "$temp/state_cooling", replace

//proximity to coast
use "$data\Amenities\Lee_lin\Supplementary\data\Lee_Lin_data", clear
keep if year == 2000
keep statefips tr_pop d2shore
replace d2shore = d2shore/100000
ren statefips stfips
collapse (mean) d2shore [w=tr_pop], by(stfips)
set obs 51
replace stfips = 2 in 50
replace stfips = 15 in 51
replace d2shore = 0 in 51
replace d2shore = 3 in 50
su d2shore
replace d2shore = d2shore - `r(min)'
save "$temp/state_d2shore", replace

//right-to-work (i.e. union power)
import excel "$data/Amenities/right_to_work.xlsx", clear
ren A state_full
drop C D
ren B year
replace state_full = subinstr(state_full, "*", "", .)
merge 1:1 state_full using "$data/Crosswalks/state_fips_crosswalk", keep(2 3) nogen
drop if statefips == 11
ren statefips stfips
keep year stfips
gen rtw_2000 = (year<2001 & year!=.)
gen rtw_2010 = (year!=.)
drop year
save "$temp/state_rtw", replace

//debt
import excel "$data/Amenities/state_debt.xlsx", clear firstrow
ren *, lower
keep thousands statedebtpercapitafy2000 statedebtpercapitafy2010
ren thousands state_full
ren *2000 debt_2000
ren *2010 debt_2010
merge 1:1 state_full using "$data/Crosswalks/state_fips_crosswalk", keep(match) nogen
drop if statefips == 11
ren statefips stfips
keep debt* stfips
replace debt_2000 = debt_2000 / 1000
su debt_2000
local mindebt = `r(min)'
replace debt_2000 = debt_2000 - `mindebt'

replace debt_2010 = debt_2010 / 1000
replace debt_2010 = debt_2010 - `mindebt'
su*
save "$temp/state_debt", replace

//college shares
use "$temp/census_master", clear
collapse (mean) coll [fweight = perwt], by(statefip)
ren coll coll_share_2000
replace coll = coll * 10
ren statefip stfips
su coll
local mincoll = `r(min)'
replace coll = coll - `mincoll'
save "$temp/state_coll_shares_2000", replace

use "$temp/acs_master", clear
keep if year>=2008 & year<=2012
collapse (mean) coll [fweight = perwt], by(statefip)
ren coll coll_share_2010
ren statefip stfips
replace coll = coll * 10
replace coll = coll - `mincoll'
save "$temp/state_coll_shares_2010", replace


//all together!
use "$temp/state_coll_shares_2000", clear
merge 1:1 stfips using "$temp/state_coll_shares_2010", keep(match) nogen
merge 1:1 stfips using "$temp/state_crime_2000", keep(match) nogen
merge 1:1 stfips using "$temp/state_crime_2010", keep(match) nogen
merge 1:1 stfips using "$temp/state_rtw", keep(match) nogen
merge 1:1 stfips using "$temp/state_debt", keep(match) nogen
merge 1:1 stfips using "$temp/state_d2shore", keep(match) nogen
merge 1:1 stfips using "$temp/state_cooling", keep(match) nogen
merge 1:1 stfips using "$temp/state_est_2010", keep(match) nogen
merge 1:1 stfips using "$temp/state_est_2000", keep(match) nogen

//export 2000 vintage
preserve
keep stfips *2000  d2shore cooling
drop stfips
order d2shore cooling crime_2000 est_2000 rtw_2000 debt_2000 coll_share_2000
export delimited "$dir/Model/utilities/amenities_2000.csv", novarn replace
restore

//export 2010 vintage
preserve
keep stfips *2010  d2shore cooling
drop stfips
order d2shore cooling crime_2010 est_2010 rtw_2010 debt_2010 coll_share_2010
export delimited "$dir/Model/utilities/amenities_2010.csv", novarn replace
restore




